/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.ControlEscolar.DAOs;

import com.ControlEscolar.Beans.Enrollment;

import java.util.ArrayList;


/**
 *
 * @author Rodna Marrufo
 */
public class EnrollmentDAO extends AbstractDAO <Enrollment>  {

     public ArrayList<Enrollment> queryByStudentKey(String studentKey) throws DAOException{
        try {
           String sql = "SELECT * FROM enrollment where subjectKey = \"" + studentKey+"\"";
           return connection.executeQuery(sql, Enrollment.class);
        } catch (Exception ex) {
            throw new DAOException();

        }

    }

     public Enrollment queryHighestSemesterEnrollment(String studentKey) throws DAOException{
        try {
            String sql = "SELECT * FROM enrollment where semester=( SELECT MAX(semester)FROM enrollment)";
            ArrayList<Enrollment> enrollmentQuery = connection.executeQuery(sql, Enrollment.class);
            return getSpecificQuery(enrollmentQuery);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw new DAOException();
        }
     }
    @Override
    public void add(Enrollment inscription) throws DAOException {
        try {
            String sql = "INSERT INTO enrollment (subjectKey,ID_Professor,studentKey,dates,state,ID_Curriculum,groupName,semester)"
                    + "values(\""
                    + inscription.getSubjectKey() + "\","
                    + inscription.getID_Professor() + ",\""
                    + inscription.getStudentKey() + "\",\""
                    + inscription.getDates() + "\",\""
                    + inscription.getState() + "\", "
                    + inscription.getID_Curriculum() + ",\""
                    + inscription.getGroupName()+ "\","
                    +inscription.getSemester()+")";
            System.out.println("aaaaaaaaaaaaaaaaaaaaaaa "+sql);
            connection.executeUpdate(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new DAOException();
        }
    }

    @Override
    public int delete(String condition) throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int update(Enrollment object) throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public ArrayList<Enrollment> query() throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    //checar
    public Enrollment querySubjectsEnrolled(String subjectKey) throws DAOException{
      try {
           String sql = "SELECT * FROM enrollment where subjectKey = \"" + subjectKey+"\" and state=\"A\"";
            ArrayList<Enrollment> enrollmentQuery=connection.executeQuery(sql, Enrollment.class);
            return getSpecificQuery(enrollmentQuery);


        } catch (Exception ex) {
            ex.printStackTrace();
            throw new DAOException();

        }
    }
    public ArrayList<Enrollment> queryActivatedErolmentsOfStudent(String studentKey,int ID_Curriculum) throws DAOException{
         try {
            String sql = "SELECT * FROM enrollment where studentKey = \""+studentKey+"\" and ID_Curriculum= "+ID_Curriculum+" and state=\"A\"";
             return connection.executeQuery(sql, Enrollment.class);


        } catch (Exception ex) {
            throw new DAOException();
        }
     }

public void disableStudentEnrollment(String studentKey) throws DAOException{
        try {
            String sql = "UPDATE enrollment SET state=  \"I\" where studentKey= \"" + studentKey + "\"";
            connection.executeUpdate(sql);
        } catch (Exception ex) {
           throw new DAOException();
        }

    }


public ArrayList<Enrollment> queryStudentEnrollmentsAtSubject(String subjectKey,String studentKey) throws DAOException{
        try {
            String sql = "SELECT * FROM enrollment where subjectKey = \"" + subjectKey
                    + "\" and studentKey=\""+studentKey+"\"";
        return connection.executeQuery(sql, Enrollment.class);

        } catch (Exception ex) {
           throw new DAOException();
        }
   }

public ArrayList<Enrollment> queryStudentEnrollmentsInSemester(String studentKey, int semester, int ID_Curriculum) throws DAOException{
    try {
            String sql = "SELECT * FROM enrollment where studentKey = \""
                    + studentKey  + "\" and semester="+semester+" and ID_Curriculum="
                    + ID_Curriculum;
        return connection.executeQuery(sql, Enrollment.class);

        } catch (Exception ex) {
           throw new DAOException();
        }
}





}
